package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import to.OrderTO;
import to.OutcomeTO;
import to.ProductTO;
import util.DBUtil;

public class ProductDAO {
	private DBUtil util=DBUtil.getInstance();
	private static ProductDAO instance = new ProductDAO();
	public ProductDAO(){
	}

	public static ProductDAO getInstance(){
		return instance;
	}
	
	public int selectCurrentProductionId() throws SQLException{
		int productionId=0;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try{
			conn = util.getConnection();
			stmt = conn.prepareStatement("select production_id.nextval from dual");
			rset = stmt.executeQuery();
			if(rset.next())
				productionId = rset.getInt(1);
		}finally{
			util.close(conn, stmt, rset);
		}
		return productionId;
	}
	 
	public ArrayList<OrderTO> selectOrderList() throws SQLException{
		ArrayList<OrderTO> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try{
			conn = util.getConnection();
			stmt = conn.prepareStatement("select o.order_id, o.order_amount, o.type_id, p.type_name, o.product_size, o.color_id, m.material_color_name, o.count_button  from orderList o left join producttype p on o.type_id = p.type_id left join materialColor m on o.color_id = m.material_color_id");
			rset = stmt.executeQuery();
			while(rset.next()){
				list.add(new OrderTO(rset.getInt(1), rset.getInt(2), rset.getString(3), rset.getString(4), rset.getInt(5), rset.getString(6), rset.getString(7), rset.getInt(8)));
			}
		}finally{
			util.close(conn, stmt, rset);
		}
		return list;
	}
	//selectProductByOrderId
	public ArrayList<ProductTO> selectProductByOrderId(int orderId) throws SQLException{
		ArrayList<ProductTO> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try{
			conn = util.getConnection();
			stmt = conn.prepareStatement("select production_id, order_id, cutting_id, sewing_id, dyeing_id, pressing_id, cutting_quality, sewing_quality, dyeing_quality, pressing_quality, cutting_time, sewing_time, deying_time, pressing_time, cutting_date, sewing_date, deying_date, pressing_date, finish_date from production where order_id=?");
			stmt.setInt(1, orderId);
			rset = stmt.executeQuery();
			while(rset.next()){
				list.add(new ProductTO(rset.getInt(1), rset.getInt(2), rset.getString(3), rset.getString(4), rset.getString(5), rset.getString(6), rset.getInt(7), rset.getInt(8), rset.getInt(9), rset.getInt(10), rset.getInt(11), rset.getInt(12), rset.getInt(13), rset.getInt(14), rset.getString(15), rset.getString(16), rset.getString(17), rset.getString(18), rset.getString(19)));
			}
		}finally{
			util.close(conn, stmt, rset);
		}
		return list;
	}
	//selectOrderListByOrderId
	public ArrayList<OrderTO> selectOrderListByOrderId(int orderId) throws SQLException{
		ArrayList<OrderTO> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try{
			conn = util.getConnection();
			stmt = conn.prepareStatement("select order_id, order_amount, type_id, product_size, color_id, count_button from orderList where order_id = ?");
			stmt.setInt(1, orderId);
			rset = stmt.executeQuery();
			if(rset.next()){
				list.add(new OrderTO(rset.getInt(1), rset.getInt(2), rset.getString(3), rset.getInt(4), rset.getString(5), rset.getInt(6)));
			}
		}finally{
			util.close(conn, stmt, rset);
		}
		return list;
	}
	//selectOutComeByOrderId
	public ArrayList<OutcomeTO> selectOutComeByOrderId(int outcomeId) throws SQLException{
		ArrayList<OutcomeTO> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try{
			conn = util.getConnection();
			stmt = conn.prepareStatement("select production_id, outcome_id, cutting_quality, sewing_quality, dyeing_quality, pressing_quality, cutting_passed, sewing_passed, dyeing_passed, pressing_passed, cutting_error, sewing_error, dyeing_error, pressing_error from outcome where outcome_id = ?");
			stmt.setInt(1, outcomeId);
			rset = stmt.executeQuery();
			while(rset.next()){
				list.add(new OutcomeTO(rset.getInt(1), rset.getInt(2), rset.getInt(3), rset.getInt(4), rset.getInt(5), rset.getInt(6), rset.getString(7), rset.getString(8), rset.getString(9), rset.getString(10), rset.getString(11), rset.getString(12), rset.getString(13), rset.getString(14)));
			}
		}finally{
			util.close(conn, stmt, rset);
		}
		return list;
	}
	//selectMaterial
	public HashMap<String, String> selectMaterial () throws SQLException{
		HashMap map = new HashMap();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try{
			conn = util.getConnection();
			stmt = conn.prepareStatement("select material_color_id, material_color_name from materialcolor");
			rset = stmt.executeQuery();
			while(rset.next()){
				map.put(rset.getString(1), rset.getString(2));
			}
		}finally{
			util.close(conn, stmt, rset);
		}
		return map;
	}
	//selectProductType
	public HashMap<String, String> selectProductType() throws SQLException{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		HashMap map = new HashMap();
		try{
			conn = util.getConnection();
			stmt = conn.prepareStatement("select * from producttype");
			rset = stmt.executeQuery();
			while(rset.next()){
				map.put(rset.getString(1), rset.getString(2));
			}
		}finally{
			util.close(conn, stmt, rset);
		}
		return map;
	}
	//insertOrder
	public int insertOrder(OrderTO oto) throws SQLException{
		Connection conn = null;
		PreparedStatement stmt = null;
		int cnt = 0;
		try{
			conn = util.getConnection();
			stmt = conn.prepareStatement("insert into orderList(order_id, type_id, product_size, color_id, count_button, order_amount) values(order_id.nextVal,?,?,?,?,?)");
			stmt.setString(1, oto.getTypeId());
			stmt.setInt(2, oto.getSize());
			stmt.setString(3, oto.getColorId());
			stmt.setInt(4, oto.getCountButtons());
			stmt.setInt(5, oto.getOrderAmount());
			cnt = stmt.executeUpdate();
		}finally{
			util.close(conn, stmt);
		}
		return cnt;
	}
	//insertProduction
	public void insertProduction(ProductTO pto) throws SQLException{
		Connection conn = null;
		PreparedStatement stmt = null;
		int cnt = 0;
		try{
			conn = util.getConnection();
			stmt = conn.prepareStatement("insert into production(production_id, order_id,"
					+"cutting_id, dyeing_id, sewing_id, pressing_id,"
					+"cutting_quality, dyeing_quality, sewing_quality, pressing_quality,"
					+"cutting_time, deying_time, sewing_time, pressing_time,"
					+"cutting_date, deying_date, sewing_date, pressing_date, finish_date) values(production_id.nextVal,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			stmt.setInt(1, pto.getOrderId());
			stmt.setString(2, pto.getCuttingId());
			stmt.setString(3, pto.getDyeingId());
			stmt.setString(4, pto.getSewingId());
			stmt.setString(5, pto.getPressingId());
			stmt.setInt(6, pto.getCuttingQuality());
			stmt.setInt(7, pto.getDyeingQuality());
			stmt.setInt(8, pto.getSewingQuality());
			stmt.setInt(9, pto.getPressingQuality());
			stmt.setInt(10, pto.getCuttingTime());
			stmt.setInt(11, pto.getDyeingTime());
			stmt.setInt(12, pto.getSewingTime());
			stmt.setInt(13, pto.getPressingTime());
			stmt.setString(14, pto.getCuttingDate());
			stmt.setString(15, pto.getDyeingDate());
			stmt.setString(16, pto.getSewingDate());
			stmt.setString(17, pto.getPressingDate());
			stmt.setString(18, pto.getFinished());
			cnt = stmt.executeUpdate();
		}finally{
			util.close(conn, stmt);
		}
	}
	//updateProduction
	public void updateProduction(ProductTO pto) throws SQLException{
		Connection conn = null;
		PreparedStatement stmt = null;
		try{
			conn = util.getConnection();
			stmt = conn.prepareStatement("update production set order_id = ?,"
					+"cutting_id = ?, dyeing_id = ?, sewing_id = ?, pressing_id = ?,"
					+"cutting_quality = ?, dyeing_quality = ?, sewing_quality = ?, pressing_quality = ?,"
					+"cutting_time = ?, deying_time = ?, sewing_time = ?, pressing_time = ?,"
					+"cutting_date = ?, deying_date = ?, sewing_date = ?, pressing_date = ?, finish_date = ? where production_id = ?");
			stmt.setInt(1, pto.getOrderId());
			stmt.setString(2, pto.getCuttingId());
			stmt.setString(3, pto.getDyeingId());
			stmt.setString(4, pto.getSewingId());
			stmt.setString(5, pto.getPressingId());
			stmt.setInt(6, pto.getCuttingQuality());
			stmt.setInt(7, pto.getDyeingQuality());
			stmt.setInt(8, pto.getSewingQuality());
			stmt.setInt(9, pto.getPressingQuality());
			stmt.setInt(10, pto.getCuttingTime());
			stmt.setInt(11, pto.getDyeingTime());
			stmt.setInt(12, pto.getSewingTime());
			stmt.setInt(13, pto.getPressingTime());
			stmt.setString(14, pto.getCuttingDate());
			stmt.setString(15, pto.getDyeingDate());
			stmt.setString(16, pto.getSewingDate());
			stmt.setString(17, pto.getPressingDate());
			stmt.setString(18, pto.getFinished());
			stmt.setInt(19, pto.getProductId());
			stmt.executeUpdate();
		}finally{
			util.close(conn, stmt);
		}

	}
	public int insertOutcome(OutcomeTO octo) throws SQLException{
		Connection conn = null;
		PreparedStatement stmt = null;
		int cnt = 0;
		try{
			conn = util.getConnection();
			stmt = conn.prepareStatement("insert into outcome(production_id, outcome_id, cutting_quality, sewing_quality, dyeing_quality, pressing_quality, cutting_passed, sewing_passed, dyeing_passed, pressing_passed, cutting_error, sewing_error, dyeing_error, pressing_error) values(?,outcome_id.nextVal,?,?,?,?,?,?,?,?,?,?,?,?)");
			stmt.setInt(1, octo.getProductId());
			stmt.setInt(2, octo.getCuttingQuality());
			stmt.setInt(3, octo.getSewingQuality());
			stmt.setInt(4, octo.getDyeingQuality());
			stmt.setInt(5, octo.getPressingQuality());
			stmt.setString(6, octo.getCuttingPassed());
			stmt.setString(7, octo.getSewingPassed());
			stmt.setString(8, octo.getDyeingPassed());
			stmt.setString(9, octo.getPressingPassed());
			stmt.setString(10, octo.getCuttingError());
			stmt.setString(11, octo.getSewingError());
			stmt.setString(12, octo.getDyeingError());
			stmt.setString(13, octo.getPressingError());
			cnt = stmt.executeUpdate();
		}finally{
			util.close(conn, stmt);
		}
		return cnt;
	}

	public ArrayList<ProductTO> selectOrderListByProductId(int productId)throws SQLException{
		ArrayList<ProductTO> list = new ArrayList<ProductTO>();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try{
			conn = util.getConnection();
			stmt = conn.prepareStatement("select p.production_id, p.order_id, o.order_id, o.order_amount, o.type_id, o.product_size, o.color_id, o.count_button, p.cutting_id, p.sewing_id, p.dyeing_id, p.pressing_id, p.cutting_quality, p.sewing_quality, p.dyeing_quality, p.pressing_quality, p.cutting_time, p.sewing_time, p.deying_time, p.pressing_time, p.cutting_date, p.sewing_date, p.deying_date, p.pressing_date, p.finish_date from production p, orderList o where p.order_id=? and p.order_id = o.order_id");
			stmt.setInt(1, productId);
			rset = stmt.executeQuery();
			while(rset.next()){	
				list.add(new ProductTO(rset.getInt(1), rset.getInt(2), new OrderTO(rset.getInt(3), rset.getInt(4), rset.getString(5), rset.getInt(6), rset.getString(7), rset.getInt(8)), rset.getString(9), rset.getString(10), rset.getString(11), rset.getString(12), rset.getInt(13), rset.getInt(14), rset.getInt(15), rset.getInt(16), rset.getInt(17), rset.getInt(18), rset.getInt(19), rset.getInt(20), rset.getString(21), rset.getString(22), rset.getString(23), rset.getString(24), rset.getString(25)));
			}
		}finally{
			util.close(conn, stmt, rset);
		}
		return list;
	}
}
